package br.ufrj.neg.reuniao.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.reuniao.bean.EncaminhamentoBean;

public class EncaminhamentoModel {
	public Collection<EncaminhamentoBean> listaEncaminhamentoReuniao(Integer idReuniao) {
			ResultSet rs = null;
			PreparedStatement ps = null;;
			Connection conn = ServiceLocator.getConnection();
			try {
				Collection<EncaminhamentoBean> col = new ArrayList<EncaminhamentoBean>();
				EncaminhamentoBean eb;
				String sql = "select * from encaminhamento where reun_cd_id = ? order by 1 asc";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, idReuniao);
				rs = ps.executeQuery();
				while(rs.next()){
					eb = new EncaminhamentoBean();
					eb.setId(rs.getInt("ENCA_CD_ID"));
					eb.setIdReuniao(rs.getInt("REUN_CD_ID"));
					eb.setDescricao(rs.getString("ENCA_TX_TEXTO"));
					col.add(eb);
				}
				
				return col;
			} catch (Exception e) {
				System.out.println(e);
				return null;
			}finally{
				try{rs.close();}catch(Exception e){System.out.println(e);}
				try{ps.close();}catch(Exception e){System.out.println(e);}
				try{conn.close();}catch(Exception e){System.out.println(e);}
			}
	}
	public EncaminhamentoBean getEncaminhamentoReuniao(Integer idReuniao) {
		ResultSet rs = null;
		PreparedStatement ps = null;;
		Connection conn = ServiceLocator.getConnection();
		try {
			EncaminhamentoBean eb = null;
			String sql = "select * from encaminhamento where reun_cd_id = ? order by 1 asc";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idReuniao);
			rs = ps.executeQuery();
			while(rs.next()){
				eb = new EncaminhamentoBean();
				eb.setId(rs.getInt("ENCA_CD_ID"));
				eb.setIdReuniao(rs.getInt("REUN_CD_ID"));
				eb.setDescricao(rs.getString("ENCA_TX_TEXTO"));
			}
			return eb;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}finally{
			try{rs.close();}catch(Exception e){System.out.println(e);}
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
}
	public void adicionarEncaminhamento(EncaminhamentoBean encaminhamento) {
		PreparedStatement ps=null;;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "insert into encaminhamento" +
						"(reun_cd_id, enca_tx_texto) values" +
						"(?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, encaminhamento.getIdReuniao());
			ps.setString(2, encaminhamento.getDescricao());
			ps.executeUpdate();
			conn.commit();

		} catch (Exception e) {
			System.out.println(e);
	
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	public void atualizaEncaminhamento(EncaminhamentoBean encaminhamento) {
		PreparedStatement ps=null;;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "update encaminhamento " +
						"set reun_cd_id = ?, " + 
						"enca_tx_texto = ? "  +
						"where enca_cd_id = ?";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, encaminhamento.getIdReuniao());
			ps.setString(2, encaminhamento.getDescricao());
			ps.setInt(3, encaminhamento.getId());
			ps.executeUpdate();
			conn.commit();

		} catch (Exception e) {
			System.out.println(e);
	
		}finally{
			try{ps.close();}catch(Exception e){System.out.println(e);}
			try{conn.close();}catch(Exception e){System.out.println(e);}
		}
	}
	
	public boolean removeEncaminhamentoPorReuniao(int idReuniao) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();

		try {
			/* Remove o encaminhamento */
			String sql = "delete from encaminhamento where reun_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idReuniao);
			System.out.println(sql);
			ps.executeUpdate();

			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}

	}
}
